﻿using Furion.Logging;
using Newtonsoft.Json;
using System.Data;
using System.Text;

namespace Admin.NET.Application.Service;

/// <summary>
/// sql查询服务,只能执行select 语句
/// </summary>
[AllowAnonymous]
[ApiDescriptionSettings(KeepName = true)]
public class SqlQueryService : IDynamicApiController
{
    protected readonly ReadOnlyDbManager _dbManager;

    public SqlQueryService(ReadOnlyDbManager dbManager)
    {
        _dbManager = dbManager;
    }

    // 根据用户提问获取查询结果
    public async Task<SqlQueryOutput> QueryData(List<SqlQueryPrompt> input)
    {
        // 1，创建提示词
        //var systemPrompt = new SqlQueryPrompt() { role = "system", content = input.SystemPrompt };
        //var userPrompt = new SqlQueryPrompt() { role = "user", content = input.UserQuestion };
        //var promptArr = new List<SqlQueryPrompt>() { systemPrompt, userPrompt };
        var prompt = JsonConvert.SerializeObject(input);
        Log.Information("模型提示词:" + prompt);
        // 2，调用大模型生成sql语句
        Console.WriteLine("请求大模型......");
        var sqlJson = await GetSQL(prompt);
        var sql = JsonConvert.DeserializeObject<SqlQueryGenerateResponse>(sqlJson).sql;
        Console.WriteLine(sql);
        Log.Information("模型创建的SQL:" + sql);
        // 对返回结果进行预处理
        // 删除```sql之前的字符
        int index = sql.IndexOf("```sql");
        if (index >= 0)
        {
            sql = sql.Substring(index + 7);
        }
        // 删除```之后的字符
        index = sql.IndexOf("```");
        if (index >= 0)
        {
            sql = sql.Substring(0, index);
        }

        // 3，执行sql语句，返回数据和列名给前端
        return QueryDataAndColumn(sql);
    }


    // 根据sql查询，返回表格数据和列名，按照arco-design-vue的table组件的格式要求
    public SqlQueryOutput QueryDataAndColumn(string sql)
    {
        List<SugarParameter> parameter = new List<SugarParameter>();
        var dt = _dbManager.QueryToDataTable(sql, parameter);

        // 把表头转前端表格组件需要的列配置json
        List<SqlQueryTableColumn> expandoColumns = ConvertDataTableColumnsToExpandoObjectList(dt);
        // 转换 DataTable 为 List<ExpandoObject>
        List<Dictionary<string, object>> expandoData = ConvertDataTableToExpandoObjectList(dt);

        return new SqlQueryOutput()
        {
            Sql = sql,
            Columns = expandoColumns,
            Data = expandoData
        };
    }

    // 把表格数据转为csv格式,用于python或大语言模型分析,提高数据传输效率,减少大语言模型的token占用
    public string Query2CSV(SqlQuerySqlInput input)
    {
        Console.WriteLine("Query2CSV入参:模型创建的SQL:");
        Console.WriteLine(input.Sql);
        Log.Information("Query2CSV入参:模型创建的SQL:" + input.Sql);
        // 对返回结果进行预处理
        // 删除```sql之前的字符
        var sql = input.Sql;
        int index = input.Sql.IndexOf("```sql");
        if (index >= 0)
        {
            sql = sql.Substring(index + 7);
        }
        // 删除```之后的字符
        index = sql.IndexOf("```");
        if (index >= 0)
        {
            sql = sql.Substring(0, index);
        }

        List<SugarParameter> parameter = new List<SugarParameter>();
        var dt = _dbManager.QueryToDataTable(sql, parameter);
        return DataTableToCSV(dt);
    }

    // 调用大模型生成sql语句
    private async Task<string> GetSQL(string promptArray)
    {
        // 创建HttpClient实例
        using (HttpClient client = new HttpClient())
        {
            // 构建查询字符串
            //string requestUri = $"http://127.0.0.1:8000/getSQL?sql={prompt}";
            string requestUri = $"http://192.168.1.190:8000/getSQLMultiRound";
            //string requestUri = $"http://192.168.2.98:8000/getSQLMultiRound";

            // 创建HttpContent对象，并设置其媒体类型为application/json
            HttpContent content = new StringContent(promptArray, Encoding.UTF8, "application/json");
            // 设置请求超时时间为39秒
            client.Timeout = TimeSpan.FromSeconds(45);
            try
            {
                // 发起请求
                HttpResponseMessage response = await client.PostAsync(requestUri, content);
                response.EnsureSuccessStatusCode();
                string responseBody = await response.Content.ReadAsStringAsync();
                // 输出响应内容
                Log.Information("模型的回答:" + responseBody);
                return responseBody;
            }
            catch (HttpRequestException e)
            {
                Log.Error("获取SQL语句失败了:" + e.ToString());
                throw Oops.Oh("获取SQL语句失败");
            }
            catch (Exception e)
            {
                Log.Error("调用大模型失败了:" + e.ToString());
                throw Oops.Oh("调用大模型失败");
            }
        }
    }

    // 把表格列转为json格式的数组
    // json格式是按照arco-design-vue的table组件
    // 示例：[{"title":"ID","dataIndex":"id"},{"title":"Name","dataIndex":"name"}]
    private static List<SqlQueryTableColumn> ConvertDataTableColumnsToExpandoObjectList(DataTable dt)
    {
        return dt.Columns.Cast<DataColumn>().Select(column =>
        new SqlQueryTableColumn
        {
            title = column.ColumnName,
            key = column.ColumnName,
            dataKey = column.ColumnName,
            width = 90 + column.ColumnName.Length * 5,
        }).ToList();
    }

    private static List<Dictionary<string, object>> ConvertDataTableToExpandoObjectList(DataTable dt)
    {
        List<Dictionary<string, object>> expandoList = new List<Dictionary<string, object>>();
        foreach (DataRow row in dt.Rows)
        {
            var expandoDict = new Dictionary<string, object>();


            foreach (DataColumn col in dt.Columns)
            {
                expandoDict[col.ColumnName] = row[col];
            }

            expandoList.Add(expandoDict);
        }

        return expandoList;
    }

    private static string DataTableToCSV(DataTable dataTable)
    {
        // 创建一个StringBuilder来存储CSV字符串
        StringBuilder csvContent = new StringBuilder();

        // 写入列名
        foreach (DataColumn column in dataTable.Columns)
        {
            csvContent.Append(column.ColumnName + ",");
        }
        csvContent.AppendLine(); // 添加换行符

        // 写入行数据
        foreach (DataRow row in dataTable.Rows)
        {
            foreach (DataColumn column in dataTable.Columns)
            {
                // 将每个单元格的值转换为字符串并添加到csvContent
                csvContent.Append(row[column.ColumnName].ToString().Replace(",", ";") + ",");
            }
            csvContent.AppendLine(); // 添加换行符
        }

        // 返回CSV格式的字符串
        return csvContent.ToString();
    }
}
